--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_par_prefnom AS (SELECT * FROM &erp.par_prefnom WHERE 1=2);


DELETE FROM se_par_prefnom;

 INSERT INTO se_par_prefnom
 (SELECT distinct SubStr(prefixe,1,5) prefnom_cod,
          0 enreg_id,
          'N' enreg_s,
          SubStr(prefixe,1,5) prefnom_lib,
          NULL prefnom_lib2,
          SubStr(prefixe,1,5) prefnomabr_lib
    FROM uv_cclients
    WHERE prefixe IS NOT null
    ) ;
 INSERT INTO se_par_prefnom
 (SELECT distinct SubStr(prefixe,1,5) prefnom_cod,
          0 enreg_id,
          'N' enreg_s,
          SubStr(prefixe,1,5) prefnom_lib,
          NULL prefnom_lib2,
          SubStr(prefixe,1,5) prefnomabr_lib
    FROM uv_cadresses a
    WHERE prefixe IS NOT null
      and not exists( select 1 from se_par_prefnom p where p.prefnom_cod=SubStr(a.prefixe,1,5))
    ) ;

 UPDATE se_par_prefnom tf SET enreg_id=(select enreg_id FROM &erp.par_prefnom f WHERE f.PREFNOM_COD=tf.PREFNOM_COD) ;
 UPDATE se_par_prefnom tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('prefnom_cod','PAR_PREFNOM','O') ;

 commit;
 
 select * from (
SELECT &outils.stragg(source) source,prefnom_cod, enreg_id, enreg_s, prefnom_lib, prefnom_lib2, prefnomabr_lib
FROM (
  SELECT 'uv' source ,g.* FROM se_par_prefnom g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.par_prefnom g
  )
GROUP BY prefnom_cod, enreg_id, enreg_s, prefnom_lib, prefnom_lib2, prefnomabr_lib
HAVING Count(1)!=2) r
ORDER BY prefnom_cod;